package com.apes.fn.scm.merchant.account.repository;

import com.apes.fn.scm.merchant.account.model.MerchantAccountSettlement;
import com.apes.framework.jpa.repository.CustomRepository;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author pengyb
 */
public interface AccountSettlementRepository extends CustomRepository<MerchantAccountSettlement, String> {
    /**
     * fetch data by max end date
     *
     * @param merchantId merchant id
     * @return MerchantAccountSettlement
     */
    @Query("select T from MerchantAccountSettlement  T where T.reconcileEndDate=(select max(reconcileEndDate)from MerchantAccountSettlement where merchant.id=?1) and T.merchant.id=?1")
    List<MerchantAccountSettlement> findByMaxReconcileEndDate(String merchantId);

    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @param startDate  date
     * @param endDate    date
     * @return MerchantAccountSettlement
     */
    @Query("select T from MerchantAccountSettlement  T where T.merchant.id=?1 and T.state='squareAccounts' and" +
            "((T.reconcileEndDate>?2 and T.reconcileEndDate<=?3) or(T.reconcileBeginDate<?2 and T.reconcileEndDate>=?3)or(T.reconcileBeginDate>?2 and T.reconcileBeginDate<=?3))")
    List<MerchantAccountSettlement> findByMerchantAndDate(String merchantId, Date startDate, Date endDate);

    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @return MerchantAccountSettlement
     */
    @Query("select new map(T.id as id ,T.merchant as merchant ,T.reconcileBeginDate as reconcileBeginDate,T.reconcileEndDate as reconcileEndDate,T.state as state )" +
            " from MerchantAccountSettlement  T where T.merchant.id=?1 and T.state='squareAccounts' and ((T.reconcileEndDate>?2 and T.reconcileEndDate<=?3) or(T.reconcileBeginDate<?2 and T.reconcileEndDate>=?3)or(T.reconcileBeginDate>?2 and T.reconcileBeginDate<=?3)) ")
    Page<Map> findByMerchantAndState(String merchantId, Date startDate, Date endDate, Pageable pageable);


    /**
     * fetch data by merchant
     *
     * @param merchantId merchant id
     * @param reconcileDate    date
     * @return MerchantAccountSettlement
     */
    @Query("select T from MerchantAccountSettlement  T where T.merchant.id=?1 and T.state<>'squareAccounts'and T.state<>'confirmation'  and T.reconcileBeginDate>?2")
    List<MerchantAccountSettlement> findByMerchantAndReconcileDate(String merchantId, Date reconcileDate);

    @Query("SELECT A.id as id FROM MerchantAccountSettlement A WHERE A.id = :id")
    Map<String, Object> findIdAndName(@Param("id") String orderId);

    /**
     * 根据结算方式获取自动创建对账单数据
     * @param settlementType 结算方式
     * @return
     */
    @Query(value = "SELECT A.ID AS contractid,A.COMPANY_ID AS companyId, A.PARTNER_ID AS merchantId " +
            "  FROM FN_CONTRACT A, FN_PARTNER_CHECK B, MAS_DEPT C, MAS_STORE D " +
            " WHERE A.PARTNER_ID = B.FN_CUSTOMER_ID " +
            "   AND B.FN_CUSTOMER_ID = C.COMPANY_ID " +
            "   AND C.ID = D.ID " +
            "   AND A.STATE = 'approve' " +
            "   AND B.STATE = 'approve' " +
            "   AND B.END_DATE >= SYSDATE " +
            "   AND C.DEPT_TYPE = '000007' " +
            "   AND D.FN_TAG IS NOT NULL AND B.PERIOD_TYPE = :settlementType" +
            "   AND (D.CLOSING_DAY IS NULL OR D.CLOSING_DAY > SYSDATE)",nativeQuery = true)
    List<Map<String, Object>> queryAutoCreateSettlement(@Param("settlementType") String settlementType);
}
